/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package de.titama.control;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Thomas Müller
 * 
 */

public class DBConnect {    
    
    public Connection c = null;
    public Statement stmt;
    
    private ResultSet UserData;
    public String[] CachedUserData;
    
    
    public DBConnect(){
        
    
    }

    
    /**************************************************************************************************************/
    
    
    /*
    * Öffnet eine Verbindung zur Datenbank
    */
    
    public void connect()
    {
        try
        {
            Class.forName("org.sqlite.JDBC");
            Connection c = DriverManager.getConnection("jdbc:sqlite:Titama.sqlite");
            stmt = c.createStatement();

        }
        catch(SQLException | ClassNotFoundException exe)
        {
            ErrorWindow(exe);
        }
    }
    
    /*
    * Schließt eine offene Verbindung zur Datenbank
    */
    
    public void disconnect()
    {
         try
        {
         stmt.close();
         c.close();
        }
        catch(Exception exe)
        {
            ErrorWindow(exe);
        }
    
    }
    
    /*
    * Überprüft, ob eine offene Verbindung zur Datenbank besteht
    */
    
    public boolean isconnected()
    {
        try
        {
            return !c.isClosed();
        }
        catch(Exception exe)
        {
            ErrorWindow(exe);
        }
        
        return false;
    }
    
    
    /**************************************************************************************************************/
    
    /**
    * Ausgabefenster für Fehlermeldungen
    * 
    * @param error Übergebene Fehlermeldung
    */
            
    public void ErrorWindow(Object error)
    {
        
    }
    
    /**************************************************************************************************************/      
    

    /**
     * 
     * Add Task to Database
     * 
     * @param name
     * @param priority
     * @param created
     * @param due
     * @param category
     * @param notes
     * 
     * @return true if Task added successful | false if not
     */
    
    public boolean addTask(String name, String priority, String created, String due, String category, String notes)
    {
        connect();
        
        try
        {
            ResultSet NewTask = stmt.executeQuery("SELECT COUNT(name) AS taskcount FROM tasks WHERE name = '" +name +"'");
            NewTask.next();
            int count = NewTask.getInt("taskcount");
            
            if(count == 0)
            {   
                stmt.executeUpdate("INSERT INTO tasks (name,priority,created,due,category,notes) VALUES ('"+name+"','"+priority+"','"+created+"','"+due+"','"+category+"','"+notes+"')");
                return true;
            }
            else
            {
               stmt.executeUpdate("UPDATE Tasks SET name = '"+name+"', priority = '"+priority+"', due = '"+due+"', category = '"+category+"', notes = '"+notes+"' WHERE name = '"+name+"'");
                
            }
        }
        catch(SQLException exe)
        {
                       exe.printStackTrace();
        }     
        
        disconnect();
        return false;

    }

    /**
     * 
     * Löscht eine Aufgabe aus der Datenbak
     * 
     * @param name 
     */
    
    public void deleteTask(String name)
    {
        connect();
        
        try
        {
            stmt.executeUpdate("DELETE FROM Tasks WHERE name = '"+name+"'"); 
        }
        catch(SQLException exe)
        {
            ErrorWindow(exe);
            exe.printStackTrace();
        }      
        
        disconnect();

    }
    
    /**
     * 
     * Holt sich alle Aufgaben aus der Datenbak
     * 
     * @return Die Aufgaben als Datentyp ArrayList<String[]>
     */
    
    
    public ArrayList<String[]> getTasks()
    {
        ArrayList<String[]> tasks = new ArrayList<>();
        String[] temp;
        int Arraypos;
        
        connect();
        
        try
        {
            ResultSet rs = stmt.executeQuery("SELECT * FROM tasks");            

            while(rs.next())
            {
                temp = new String[6];
                Arraypos = 0;
                
                for(int i=0;i<6;i++)
                {
                    temp[Arraypos] = rs.getString(i+1);
                    Arraypos++;
                }
                
                tasks.add(temp);
            }
            
        }
        catch(SQLException exe)
        {
            ErrorWindow(exe);
        }
        
        disconnect();
        return tasks;

    }
    
    /**
     * 
     * Holt sich die Notitz zur übergebenen Aufgabe 
     * 
     * @param task  Aufgabe dessen Notiz geholt werden soll
     * @return 
     */
    
    public String getNotes(String task)
    {
        String notes = "";  
        
        connect();
        
        try
        {
            ResultSet rs = stmt.executeQuery("SELECT notes FROM Tasks where name = '" +task +"'");            

            while(rs.next())
            {
                
                notes = rs.getString("notes");
            }
            
        }
        catch(SQLException exe)
        {
            ErrorWindow(exe);
        }
        
        disconnect();
        return notes;

    }
    
    
    /**************************************************************************************************************/ 
    
        public boolean addCategory (String name)
    {
        connect();
        
        try
        {
            ResultSet NewCategory = stmt.executeQuery("SELECT COUNT(category) AS catcount FROM Categorys WHERE category = '" +name +"'");
            NewCategory.next();
            int count = NewCategory.getInt("catcount");
            
            if(count == 0)
            {   
                stmt.executeUpdate("INSERT INTO Categorys (category) VALUES ('"+name+"')");
                return true;
            }
            else
            {
                System.out.println("User allready exists");
            }
        }
        catch(SQLException exe)
        {
            ErrorWindow(exe);
            exe.printStackTrace();
        }     
        
        disconnect();
        return false;

    }
    
    /**
     *
     * @return
     */
    
    public ArrayList<String> getCategorys()
    {
        ArrayList<String> categorys = new ArrayList<>();     
        
        connect();
        
        try
        {
            ResultSet rs = stmt.executeQuery("SELECT * FROM Categorys");            

            while(rs.next())
            {
                
                categorys.add(rs.getString("category"));
            }
            
        }
        catch(SQLException exe)
        {
            ErrorWindow(exe);
        }
        
        disconnect();
        return categorys;

    }
    
    
    
    public void deleteCategory(String category)
    {
        connect();
        
        try
        {
            stmt.executeUpdate("DELETE FROM Categorys WHERE category = '"+category+"'"); 
        }
        catch(SQLException exe)
        {
            ErrorWindow(exe);
        }      
        
        disconnect();

    }
    
    
      /**************************************************************************************************************/ 
    
       public int PrioCount(String prio)
    {
        int helper = 0;
        
        connect();
        
        try
        {
            ResultSet Count = stmt.executeQuery("SELECT COUNT(priority) AS count FROM Tasks WHERE priority = '"+prio+"'");
            Count.next();
            helper = Count.getInt("count");
        }
        catch(SQLException exe)
        {
            ErrorWindow(exe);
            exe.printStackTrace();
        }      
        
        disconnect();
        
        return helper;

    }
       
    public int CatCount(String Cat)
    {
        int helper = 0;
        
        connect();
        
        try
        {
            ResultSet Count = stmt.executeQuery("SELECT COUNT(category) AS count FROM Tasks WHERE category = '"+Cat+"'");
            Count.next();
            helper = Count.getInt("count");
        }
        catch(SQLException exe)
        {
            ErrorWindow(exe);
           
        }      
        
        disconnect();
        
        return helper;

    }
    
    public int TaskCount()
    {
        int helper = 0;
        
        connect();
        
        try
        {
            ResultSet Count = stmt.executeQuery("SELECT COUNT(name) AS count FROM Tasks");
            Count.next();
            helper = Count.getInt("count");
        }
        catch(SQLException exe)
        {
            ErrorWindow(exe);
           
        }      
        
        disconnect();
        
        return helper;

    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    public static void main(String[] args)
    {
        DBConnect con = new DBConnect();
        
        //con.addTask("Pizza bestellen","wichtig", "05.12.2014", "12.12.2014", "Bestellungen", "bla bli blub");
        System.out.println(con.PrioCount("Niedrig"));
    
    
    
    
    }
    
    
}
